package com.xu.a2019;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

@SuppressWarnings("ALL")
public class SqlHelper extends SQLiteOpenHelper {
    private static final String TAG = "SqlHelper";
    private static SqlHelper INSTANCE;
    private final ICallBack callBack;


    private SqlHelper(Context context, ICallBack callBack) {
        super(context, callBack.getDatabaseName(), null, callBack.getVersion());
        this.callBack = callBack;
    }

    public static void init(@NonNull Context context, @NonNull ICallBack callBack) {
        INSTANCE = new SqlHelper(context, callBack);
    }


    public static <T> void insert(String tableName, T entity) {
        SQLiteDatabase db = INSTANCE.getWritableDatabase();
        db.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            INSTANCE.callBack.assignValuesByEntity(tableName, entity, values);
            db.insert(tableName, null, values);
            values.clear();
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
//            db.close();
        }
    }


    public static <T> void insert(String tableName, List<T> entities) {
        SQLiteDatabase db = INSTANCE.getWritableDatabase();
        db.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            for (T entity : entities) {
                INSTANCE.callBack.assignValuesByEntity(tableName, entity, values);
                db.insert(tableName, null, values);
                values.clear();
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
//            db.close();
        }
    }

    public static <T> void update(String tableName, T entity, String whereClause, String[] whereArgs) {
        SQLiteDatabase db = INSTANCE.getWritableDatabase();
        db.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            INSTANCE.callBack.assignValuesByEntity(tableName, entity, values);
            db.update(tableName, values, whereClause, whereArgs);
            values.clear();
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
//            db.close();
        }
    }

    public static List<PhraseCache> selectByKey(String tableName,String key) {
        String sql = "select * from " +tableName + " where date = ? ";
        String[] where = new String[]{key};
        List<PhraseCache> lists = query(tableName, sql, where);
        return lists;
    }

    public static void updateKey(String tableName, PhraseCache entity) {
        String[] where = new String[]{entity.getmDate()};
        update(tableName, entity, PhraseEntry.COLEUM_NAME_DATE+" = ? ", where);
    }

    public static <T> List<T> query(String tableName, @NonNull String queryStr, @Nullable String[] whereArgs) {
        SQLiteDatabase db = INSTANCE.getReadableDatabase();
        Cursor cursor = db.rawQuery(queryStr, whereArgs);
        try {
            List<T> lists = new ArrayList<>(cursor.getCount());
            if (cursor.moveToFirst()) {
                do {
                    T entity = INSTANCE.callBack.newEntityByCursor(tableName, cursor);
                    if (entity != null) {
                        lists.add(entity);
                    }
                } while (cursor.moveToNext());
            }
            return lists;
        } finally {
            cursor.close();
//            db.close();
        }
    }

    public static void deleteFrom(String tableName) {

        SQLiteDatabase db = INSTANCE.getWritableDatabase();
        db.beginTransaction();
        try {
            String sql = "DELETE FROM " + tableName;
            db.execSQL(sql);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
//            db.close();
        }
    }

    // delete的适用场合是涉及到删除的对象数量较少时。
    // 当删除多条数据时（例如：500条），通过循环的方式来一个一个的删除需要12s，而使用execSQL语句结合(delete from table id in("1", "2", "3"))的方式只需要50ms
    public static void delete(String tableName, String whereClause, String[] whereArgs) {
        SQLiteDatabase db = INSTANCE.getWritableDatabase();
        db.beginTransaction();
        try {
            db.delete(tableName, whereClause, whereArgs);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
//            db.close();
        }
    }

    /*
     * 当操作数据较多时，直接使用sql语句或许效率更高
     *
     * 执行sql语句（例如: String sql = "delete from tableName where mac in ('24:71:89:0A:DD:82', '24:71:89:0A:DD:83','24:71:89:0A:DD:84')"）
     * 注意：db.execSQL文档中有说明"the SQL statement to be executed. Multiple statements separated by semicolons are not supported."，
     * 也就是说通过分号分割的多个statement操作是不支持的。
     *
     */
    public static void execSQL(String sql) {
        SQLiteDatabase db = INSTANCE.getWritableDatabase();
        db.beginTransaction();
        try {
            db.execSQL(sql);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
//            db.close();
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        List<String> tablesSQL = callBack.createTablesSQL();
        if (tablesSQL==null||tablesSQL.size()==0){
            db.execSQL(SqlCallBack.TABLE_NAME_PHRASE);
        }
        for (String createTable : tablesSQL) {
            db.execSQL(createTable);
            Log.d(TAG, "create table " + "[ \n" + createTable + "\n ]" + " successful! ");
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        callBack.onUpgrade(sqLiteDatabase, oldVersion, newVersion);
    }

    public interface ICallBack {
        String getDatabaseName();

        int getVersion();

        void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);

        List<String> createTablesSQL();

        <T> void assignValuesByEntity(String tableName, T entity, ContentValues values);

        <T> T newEntityByCursor(String tableName, Cursor cursor);
    }
}
